package com.plsjavaweb.dao.implementation;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.plsjavaweb.dao.interfaces.EmployeeSeatDAO;
import com.plsjavaweb.dao.mapper.EmployeeSeatMapper;
import com.plsjavaweb.dao.mapper.Mapper;
import com.plsjavaweb.dao.utilities.DAO;
import com.plsjavaweb.pojo.EmployeeSeatPOJO;
import com.plsjavaweb.logging.LoggingConstants;


public class EmployeeSeatDAOImpl implements EmployeeSeatDAO {
	static Logger log = Logger.getLogger(EmployeeSeatDAOImpl.class.getName());
	
	//Implements get EmployeeSeats
	public List<EmployeeSeatPOJO> getEmployeeSeatsByBldgByFloor(String locationCode, String floor) {
		log.info(LoggingConstants.entryMessage);
		StringBuilder selectEmployeeSeats = new StringBuilder(
				"SELECT emp.employee_id, "
						+ "emp.email_add, "
						+ "emp.first_name, "
						+ "emp.last_name, "
						+ "emp.position_code, "
						+ "emp.status, "
						+ "seat.quadrant, "
						+ "seat.seat_code, "
						+ "seat.local_no, "
						+ "seat.col, "
						+ "seat.row, "
						+ "st.seat_type_code, "
						+ "st.seat_type_desc, "
						+ "p.project_code, "
						+ "p.project_desc, "
						+ "ld.floor_level, "
						+ "l.location_address, "
						+ "l.location_code "
						+ "FROM employee emp "
						+ "JOIN employee_seat es ON es.employee_id = emp.employee_id "
						+ "JOIN seat ON seat.seat_id = es.seat_id JOIN seat_type st ON seat.seat_type_id = st.seat_type_id "
						+ "LEFT OUTER JOIN project p ON p.project_id = seat.project_id "
						+ "JOIN location_detail ld ON ld.location_detail_id = seat.location_detail_id "
						+ "JOIN location l ON l.location_id = ld.location_id "
						+ "WHERE ld.floor_level = '" + floor + "' AND l.location_code = '" + locationCode + "'");
		List<EmployeeSeatPOJO> employeeSeats = null;
		DAO<EmployeeSeatPOJO> dataHelper = new DAO<>();
		Mapper<EmployeeSeatPOJO> empSeatMapper = new EmployeeSeatMapper();
		try {
			employeeSeats = dataHelper.ExecuteQuery(selectEmployeeSeats.toString(), empSeatMapper);
		}
		catch (Exception e) {
			log.error(LoggingConstants.catchMessage);
		}
		finally{
			log.info(LoggingConstants.exitMessage);
			log.info("returned" + employeeSeats);
			return employeeSeats;
		}

	}

	@Override
	public List<EmployeeSeatPOJO> getEmployeeSeatsByProject(int projectID) {
		log.info(LoggingConstants.entryMessage);
		StringBuilder selectEmployeeSeats = new StringBuilder(
				"SELECT emp.employee_id, "
						+ "emp.email_add, "
						+ "emp.first_name, "
						+ "emp.last_name, "
						+ "emp.position_code, "
						+ "emp.status, "
						+ "seat.quadrant, "
						+ "seat.seat_code, "
						+ "seat.local_no, "
						+ "seat.col, "
						+ "seat.row, "
						+ "st.seat_type_code, "
						+ "st.seat_type_desc, "
						+ "p.project_code, "
						+ "p.project_desc, "
						+ "ld.floor_level, "
						+ "l.location_address, "
						+ "l.location_code "
						+ "FROM employee emp "
						+ "JOIN employee_seat es ON es.employee_id = emp.employee_id "
						+ "JOIN seat ON seat.seat_id = es.seat_id JOIN seat_type st ON seat.seat_type_id = st.seat_type_id "
						+ "LEFT OUTER JOIN project p ON p.project_id = seat.project_id "
						+ "JOIN location_detail ld ON ld.location_detail_id = seat.location_detail_id "
						+ "JOIN location l ON l.location_id = ld.location_id "
						+ "WHERE seat.project_id = " + projectID + ";");
		List<EmployeeSeatPOJO> employeeSeats = null;
		DAO<EmployeeSeatPOJO> dataHelper = new DAO<>();
		Mapper<EmployeeSeatPOJO> empSeatMapper = new EmployeeSeatMapper();
		try {
			employeeSeats = dataHelper.ExecuteQuery(selectEmployeeSeats.toString(), empSeatMapper);
		}
		catch (Exception e) {
			log.error(LoggingConstants.catchMessage);
		}
		finally{
			log.info(LoggingConstants.exitMessage);
			log.info("returned" + employeeSeats);
			return employeeSeats;
		}
	}
	
	@Override
	public List<EmployeeSeatPOJO> getEmployeeSeatsByLikeEmployeeFirstName(String searchString) {
		log.info(LoggingConstants.entryMessage);
		StringBuilder selectEmployeeSeats = new StringBuilder(
				"SELECT emp.employee_id, "
						+ "emp.email_add, "
						+ "emp.first_name, "
						+ "emp.last_name, "
						+ "emp.position_code, "
						+ "emp.status, "
						+ "seat.quadrant, "
						+ "seat.seat_code, "
						+ "seat.local_no, "
						+ "seat.col, "
						+ "seat.row, "
						+ "st.seat_type_code, "
						+ "st.seat_type_desc, "
						+ "p.project_code, "
						+ "p.project_desc, "
						+ "ld.floor_level, "
						+ "l.location_address, "
						+ "l.location_code "
						+ "FROM employee emp "
						+ "JOIN employee_seat es ON es.employee_id = emp.employee_id "
						+ "JOIN seat ON seat.seat_id = es.seat_id JOIN seat_type st ON seat.seat_type_id = st.seat_type_id "
						+ "LEFT OUTER JOIN project p ON p.project_id = seat.project_id "
						+ "JOIN location_detail ld ON ld.location_detail_id = seat.location_detail_id "
						+ "JOIN location l ON l.location_id = ld.location_id "
						+ "WHERE emp.first_name like '%" + searchString + "%'");
		List<EmployeeSeatPOJO> employeeSeats = new ArrayList<>();
		DAO<EmployeeSeatPOJO> dataHelper = new DAO<>();
		Mapper<EmployeeSeatPOJO> empSeatMapper = new EmployeeSeatMapper();
		try {
			employeeSeats = dataHelper.ExecuteQuery(selectEmployeeSeats.toString(), empSeatMapper);
		}
		catch (Exception e) {
			log.error(LoggingConstants.catchMessage);
		}
		finally{
			log.info(LoggingConstants.exitMessage);
			log.info("returned" + employeeSeats);
			return employeeSeats;
		}
	}
	
	@Override
	public List<EmployeeSeatPOJO> getEmployeeSeatsByLikeEmployeeLastName(String searchString) {
		log.info(LoggingConstants.entryMessage);
		StringBuilder selectEmployeeSeats = new StringBuilder(
				"SELECT emp.employee_id, "
						+ "emp.email_add, "
						+ "emp.first_name, "
						+ "emp.last_name, "
						+ "emp.position_code, "
						+ "emp.status, "
						+ "seat.quadrant, "
						+ "seat.seat_code, "
						+ "seat.local_no, "
						+ "seat.col, "
						+ "seat.row, "
						+ "st.seat_type_code, "
						+ "st.seat_type_desc, "
						+ "p.project_code, "
						+ "p.project_desc, "
						+ "ld.floor_level, "
						+ "l.location_address, "
						+ "l.location_code "
						+ "FROM employee emp "
						+ "JOIN employee_seat es ON es.employee_id = emp.employee_id "
						+ "JOIN seat ON seat.seat_id = es.seat_id JOIN seat_type st ON seat.seat_type_id = st.seat_type_id "
						+ "LEFT OUTER JOIN project p ON p.project_id = seat.project_id "
						+ "JOIN location_detail ld ON ld.location_detail_id = seat.location_detail_id "
						+ "JOIN location l ON l.location_id = ld.location_id "
						+ "WHERE emp.last_name like '%" + searchString + "%'");
		List<EmployeeSeatPOJO> employeeSeats = new ArrayList<>();
		DAO<EmployeeSeatPOJO> dataHelper = new DAO<>();
		Mapper<EmployeeSeatPOJO> empSeatMapper = new EmployeeSeatMapper();
		try {
			employeeSeats = dataHelper.ExecuteQuery(selectEmployeeSeats.toString(), empSeatMapper);
		}
		catch (Exception e) {
			log.error(LoggingConstants.catchMessage);
		}
		finally{
			log.info(LoggingConstants.exitMessage);
			log.info("returned" + employeeSeats);
			return employeeSeats;
		}
	}

	@Override
	public List<EmployeeSeatPOJO> getEmployeeSeatsByEmployeeId(int employeeId) {
		StringBuilder selectEmployeeSeats = new StringBuilder(
				"SELECT emp.employee_id, "
						+ "emp.email_add, "
						+ "emp.first_name, "
						+ "emp.last_name, "
						+ "emp.position_code, "
						+ "emp.status, "
						+ "seat.quadrant, "
						+ "seat.seat_code, "
						+ "seat.local_no, "
						+ "seat.col, "
						+ "seat.row, "
						+ "st.seat_type_code, "
						+ "st.seat_type_desc, "
						+ "p.project_code, "
						+ "p.project_desc, "
						+ "ld.floor_level, "
						+ "l.location_address, "
						+ "l.location_code "
						+ "FROM employee emp "
						+ "JOIN employee_seat es ON es.employee_id = emp.employee_id "
						+ "JOIN seat ON seat.seat_id = es.seat_id JOIN seat_type st ON seat.seat_type_id = st.seat_type_id "
						+ "LEFT OUTER JOIN project p ON p.project_id = seat.project_id "
						+ "JOIN location_detail ld ON ld.location_detail_id = seat.location_detail_id "
						+ "JOIN location l ON l.location_id = ld.location_id "
						+ "WHERE emp.employee_id = '" + Integer.toString(employeeId) + "'");
		List<EmployeeSeatPOJO> employeeSeats = new ArrayList<>();
		DAO<EmployeeSeatPOJO> dataHelper = new DAO<>();
		Mapper<EmployeeSeatPOJO> empSeatMapper = new EmployeeSeatMapper();
		try {
			employeeSeats = dataHelper.ExecuteQuery(selectEmployeeSeats.toString(), empSeatMapper);
		}
		catch (Exception e) {
			log.error(LoggingConstants.catchMessage);
		}
		finally{
			log.info(LoggingConstants.exitMessage);
			log.info("returned" + employeeSeats);
			return employeeSeats;
		}
	}

	@Override
	public List<EmployeeSeatPOJO> getEmployeeSeatsByProjectId(int projectId) {
		StringBuilder selectEmployeeSeats = new StringBuilder(
				"SELECT emp.employee_id, "
						+ "emp.email_add, "
						+ "emp.first_name, "
						+ "emp.last_name, "
						+ "emp.position_code, "
						+ "emp.status, "
						+ "seat.quadrant, "
						+ "seat.seat_code, "
						+ "seat.local_no, "
						+ "seat.col, "
						+ "seat.row, "
						+ "st.seat_type_code, "
						+ "st.seat_type_desc, "
						+ "p.project_code, "
						+ "p.project_desc, "
						+ "ld.floor_level, "
						+ "l.location_address, "
						+ "l.location_code "
						+ "FROM employee emp "
						+ "JOIN employee_seat es ON es.employee_id = emp.employee_id "
						+ "JOIN seat ON seat.seat_id = es.seat_id JOIN seat_type st ON seat.seat_type_id = st.seat_type_id "
						+ "LEFT OUTER JOIN project p ON p.project_id = seat.project_id "
						+ "JOIN location_detail ld ON ld.location_detail_id = seat.location_detail_id "
						+ "JOIN location l ON l.location_id = ld.location_id "
						+ "WHERE p.project_id = '" + Integer.toString(projectId) + "'");
		List<EmployeeSeatPOJO> employeeSeats = new ArrayList<>();
		DAO<EmployeeSeatPOJO> dataHelper = new DAO<>();
		Mapper<EmployeeSeatPOJO> empSeatMapper = new EmployeeSeatMapper();
		try {
			employeeSeats = dataHelper.ExecuteQuery(selectEmployeeSeats.toString(), empSeatMapper);
		}
		catch (Exception e) {
			log.error(LoggingConstants.catchMessage);
		}
		finally{
			log.info(LoggingConstants.exitMessage);
			log.info("returned" + employeeSeats);
			return employeeSeats;
		}
	}

	@Override
	public List<EmployeeSeatPOJO> getEmployeeSeatsByProjectName(
			String searchString) {
		StringBuilder selectEmployeeSeats = new StringBuilder(
				"SELECT emp.employee_id, "
						+ "emp.email_add, "
						+ "emp.first_name, "
						+ "emp.last_name, "
						+ "emp.position_code, "
						+ "emp.status, "
						+ "seat.quadrant, "
						+ "seat.seat_code, "
						+ "seat.local_no, "
						+ "seat.col, "
						+ "seat.row, "
						+ "st.seat_type_code, "
						+ "st.seat_type_desc, "
						+ "p.project_code, "
						+ "p.project_desc, "
						+ "ld.floor_level, "
						+ "l.location_address, "
						+ "l.location_code "
						+ "FROM employee emp "
						+ "JOIN employee_seat es ON es.employee_id = emp.employee_id "
						+ "JOIN seat ON seat.seat_id = es.seat_id JOIN seat_type st ON seat.seat_type_id = st.seat_type_id "
						+ "LEFT OUTER JOIN project p ON p.project_id = seat.project_id "
						+ "JOIN location_detail ld ON ld.location_detail_id = seat.location_detail_id "
						+ "JOIN location l ON l.location_id = ld.location_id "
						+ "WHERE p.project_desc like '%" + searchString + "%'");
		List<EmployeeSeatPOJO> employeeSeats = new ArrayList<>();
		DAO<EmployeeSeatPOJO> dataHelper = new DAO<>();
		Mapper<EmployeeSeatPOJO> empSeatMapper = new EmployeeSeatMapper();
		try {
			employeeSeats = dataHelper.ExecuteQuery(selectEmployeeSeats.toString(), empSeatMapper);
		}
		catch (Exception e) {
			log.error(LoggingConstants.catchMessage);
			System.out.println("exception caught"+ e.getMessage());
		}
		finally{
			log.info(LoggingConstants.exitMessage);
			log.info("returned" + employeeSeats);
			return employeeSeats;
		}
	}
}
